<?php

/**
 * @filesource Communicates with the Contact control file to know which student
 * data to get and set. It is the only file that has the SQL queries and MySQL
 * connections
 *
 * @copyright 2011 by Dan, Steven, Brice, Mike, and Aaron.  This program is part
 * of Internship 2 Career, which is free software. It comes with absolutely no
 * warranty. You can redistribute or modify it under the terms of the GNU
 * General Public License as published by the Free Software Foundation (For
 * more information, see <http://www.gnu.org/licenses/).
 *
 * @version February 22, 2011
 *
 * @author Aaron Jarzombek and Brice Rader
 */

/**
 * This is the database file.
 * It contacts the control file to know
 * which contact data to get and set.
 * It is the only file that has the SQL queries and MySQL connections
 */
class dbOrganization {

   static function createTable() {
      //Create table organization
      $query = "
            create table organization(
                organizationID          int             NOT NULL AUTO_INCREMENT,
                organizationName        varchar(45)     NOT NULL,
                description             text            NULL,
                address                 varchar(45)     NOT NULL,
                address1                varchar(45)     NULL,
                city                    varchar(25)     NOT NULL,
                stateName               varchar(2)      NOT NULL,
                zip                     varchar(10)	NOT NULL,
                constraint PK_Organization PRIMARY KEY(organizationID)
                );
        ";
      connectToDatabase();
      $result = mysql_query($query);
      mysql_close();
      if ($result == false) {
         echo mysql_error();
         return false;
      }
      //mysql_close();
      //Previous query was successful
      return true;
   }

   /**
    * Retrieves all the data in the table organization
    * @return Resource object that contains retrieved data. If query is fails,
    * it returns false.
    */
   function getAllOrganizations() {
      $search = $_SESSION['search'];
      $sort = $_SESSION['sortBy'];

      $query = dbOrganization::buildQuery($search, $sort);
      connectToDatabase();
      $result = mysql_query($query);
      mysql_close();
      if ($result == false) {
         echo mysql_error();
         return false;
      }

      //Query was successful.
      return $result;
   }

   /**
   * Builds query
   * @param $search
   * @param $sort
   */
   function buildQuery($search, $sort) {

      $query = "SELECT * FROM organization WHERE organizationName LIKE '%$search%' OR city LIKE '%$search%' OR description LIKE '%$search%' ";

      //need to order by $sort
      switch ($sort) {
         case 'name_asc':
            $query .= "ORDER BY organizationName ASC";
            break;
         case 'name_desc':
            $query .= "ORDER BY organizationName DESC";
            break;
         case 'city_asc':
            $query .= "ORDER BY city ASC";
            break;
         case 'city_desc':
            $query .= "ORDER BY city DESC";
            break;
         case 'add1_desc':
            $query .= "ORDER BY address DESC";
            break;
         case 'add1_asc':
            $query .= "ORDER BY address1 ASC";
            break;
         case 'add2_desc':
            $query .= "ORDER BY address1 DESC";
            break;
         case 'add2_asc':
            $query .= "ORDER BY address1 ASC";
            break;
         case 'zip_asc':
            $query .= "ORDER BY zip ASC";
            break;
         case 'zip_desc':
            $query .= "ORDER BY zip DESC";
            break;
         case 'desc_asc':
            $query .= "ORDER BY description ASC";
            break;
         case 'desc_desc':
            $query .= "ORDER BY description DESC";
            break;
      }

      return $query;
   }

   /**
    * Gets the organization ID
	* @param $o
    * @return object Returns MySQL data object
    */
   function getOrganizationID($o) {
      $result = mysql_query("SELECT organizationID FROM organization WHERE
            organizationID='$o'");
      $row = mysql_fetch_row($result);
      return $row['0'];
   }

   function getOrganizationName($o) {
      $query = "SELECT organizationName FROM organization
        WHERE organizationID='$o'";
      connectToDatabase();
      $result = mysql_query($query);
      mysql_close();
      if ($result == false) {
         echo mysql_error();
         return false;
      }
      //Query was successful.
      return $result;
   }

   /**
   * Deletes an ID from organization
   * @param $id
   * @return true or false
   */
   function deleteFromOrganization($id) {

      //Compose the DELETE FROM query string.
      $query = sprintf("DELETE FROM organization WHERE organizationID=%d", $id);
      connectToDatabase();
      $result = mysql_query($query);

      if ($result == false) {
         echo mysql_error();
         mysql_close();
         return false;
      }
      //Query was successful.
      return true;
   }

   /**
   * Gets description
   * @param $o
   * @return object returns description
   */
   function getDescription($o) {
      $query = "SELECT description FROM organization
        WHERE organizationID='$c'";
      connectToDatabase();
      $result = mysql_query($query);
      mysql_close();
      if ($result == false) {
         echo mysql_error();
         return false;
      }
      //Query was successful.
      return $result;
   }

   /**
   * Gets address
   * @param $o
   * @return object returns address
   */
   function getAddress($o) {
      $query = "SELECT address FROM organization
        WHERE organizationID='$c'";
      connectToDatabase();
      $result = mysql_query($query);
      mysql_close();
      if ($result == false) {
         echo mysql_error();
         return false;
      }
      //Query was successful.
      return $result;
   }

   function getAddress1($o) {
      $query = "SELECT address1 FROM organization
        WHERE organizationID='$c'";
      connectToDatabase();
      $result = mysql_query($query);
      mysql_close();
      if ($result == false) {
         echo mysql_error();
         return false;
      }
      //Query was successful.
      return $result;
   }

   /**
   * Gets city
   * @param $o
   * @return object returns city
   */
   function getCity($o) {
      $query = "SELECT city FROM organization
        WHERE organizationID='$c'";
      connectToDatabase();
      $result = mysql_query($query);
      mysql_close();
      if ($result == false) {
         echo mysql_error();
         return false;
      }
      //Query was successful.
      return $result;
   }

   /**
   * Gets state name
   * @param $o
   * @return object returns state name
   */
   function getStateName($o) {
      $query = "SELECT stateName FROM organization
        WHERE organizationID='$c'";
      connectToDatabase();
      $result = mysql_query($query);
      mysql_close();
      if ($result == false) {
         echo mysql_error();
         return false;
      }
      //Query was successful.
      return $result;
   }

   /**
   * Gets zip code
   * @param $o
   * @return object returns zip code
   */
   function getZip($o) {
      $query = "SELECT zip FROM organization
        WHERE organizationID='$c'";
      connectToDatabase();
      $result = mysql_query($query);
      mysql_close();
      if ($result == false) {
         echo mysql_error();
         return false;
      }
      //Query was successful.
      return $result;
   }

   /**
    * Updates organization information.
    * @param object $o Class Organization
    * @return int Returns FALSE if there was an error updating,
    * else returns TRUE
    */
   function updateOrganization($id, $o) {
      if (!($o instanceof Organization)) {
         return false;
      }
      $organizationID = $id;
      $organizationName = $o->getOrganizationName();
      $description = $o->getDescription();
      $address = $o->getAddress();
      $address1 = $o->getAddress1();
      $city = $o->getCity();
      $stateName = $o->getState();
      $zip = $o->getZip();

      connectToDatabase();

      if ($organizationID == '') {
         return false;
      } else {
         if ($organizationName != '') {
            if (!mysql_query("UPDATE organization SET organizationName=
                        '$organizationName'
                        WHERE organizationID=$organizationID"))
               die("1Could not update organization with ID =
                        $organizationID");
         }
         if (!mysql_query("UPDATE organization SET description=
                        '$description'
                        WHERE organizationID=$organizationID"))
            die("2Could not update organization with ID =
                        $organizationID");

         if (!mysql_query("UPDATE organization SET address='$address'
                        WHERE organizationID=$organizationID"))
            die("3could not update organization with ID =
                        $organizationID");


         if (!mysql_query("UPDATE organization SET address1='$address1'
                        WHERE organizationID=$organizationID"))
            die("4could not update organization with ID =
                        $organizationID");

         if ($city != '') {
            if (!mysql_query("UPDATE organization SET city='$city' WHERE
                        organizationID=$organizationID"))
               die("5could not update organization with ID =
                        $organizationID");
         }
         if ($stateName != '') {
            if (!mysql_query("UPDATE organization SET stateName='$stateName'
                        WHERE
                        organizationID=$organizationID"))
               die("6could not update organization with ID =
                        $organizationID");
         }
         if ($zip != '') {
            if (!mysql_query("UPDATE organization SET zip='$zip' WHERE
                        organizationID=$organizationID"))
               die("7could not update organization with ID =
                        $organizationID");
         }
         mysql_close();
         return true;
      }
   }

   function getCities() {
      connectToDatabase();
      return mysql_query("SELECT DISTINCT city FROM organization");
   }

   /**
    * Inserts data in the organization
    *
    * @param organization $o The data to be inserted. It has values for all the
    * columns in the table, execpt for the prigramy key value, which is of type
    * auto-increment. It is assumed that the data does not exist in the table.
    *
    * @return true if the query is successful.
    */
   static function addOrganization($o, &$id = null) {
      if (!$o instanceof Organization) {
         echo ("Invalid argument addOrganization() function<br />");
         return false;
      }

      //Compose the INSERT INTO query string. Primary key value is missing.
      //Database server automatically generates a unique ID for the primary
      //key.
      $query = sprintf("INSERT INTO organization VALUES (null,'%s', '%s', 
            '%s', '%s', '%s', '%s', '%s')", $o->getOrganizationName(), $o->getDescription(), $o->getAddress(), $o->getAddress1(), $o->getCity(), $o->getState(), $o->getZip()
      );
      connectToDatabase();
      $result = mysql_query($query);
      //printf("->Organization added has id %d\n<br />", mysql_insert_id());
      $id = mysql_insert_id();
      mysql_close();
      if ($result == false) {
         echo mysql_error();
         return false;
      }
      //Query was successful.
      return true;
   }

   function getOrgID($o) {
      if (!($o instanceof Organization)) {
         return false;
      }
      $organizationName = $o->getOrganizationName();
      connectToDatabase();
      $result = mysql_query("SELECT organizationID FROM organization WHERE
                organizationName='$organizationName'");
      mysql_close();
      $row = mysql_fetch_assoc($result);
      return $row['organizationID'];
   }

   function getOrganizationByName($organizationName) {
      connectToDatabase();
      $result = mysql_query("SELECT organizationID FROM organization WHERE
                organizationName='$organizationName'");
      mysql_close();
      $row = mysql_fetch_assoc($result);
      return $row;
   }

   /**
   * Display organization name
   */
   function displayOrg($row) {
      connectToDatabase();
      echo ("Name: $row ['organizationName']<br />");
      mysql_close();
   }

   /**
    * Checks to see if the ID exists.
    * @param object $o Class Organization
    * @return int Returns FALSE if ID cannot be found, and TRUE if ID is found
    */
   function checkIfExists($o) {
      if (!($o instanceof Organization)) {
         return false;
      }
      $organizationName = $o->getOrganizationName();
      connectToDatabase();
      $result = mysql_query("SELECT * FROM organization WHERE
                organizationName='$organizationName'");
      $row = mysql_fetch_assoc($result);
      if (!$result) {
         mysql_close();
         return false;
      } else {
         return $row['organizationID'];
      }
   }

   /**
    * Gets all the organization information for an organization with a
    * certain ID.
    * @param int $organizationID ID of an organization you want to get.
    * @return object Returns FALSE if ID does not exist, or an Organization
    * object if it does
    */
   function getOrganization($organizationName) {
      connectToDatabase();
      $result = mysql_query("SELECT * FROM organization WHERE
                organizationName='$organizationName'");
      mysql_close();
      if (!$result) {
         return false; //organizaationID does not exist
      } else {
         $row = mysql_fetch_assoc($result);
         $o = new Organization($row['organizationName'], $row['description'],
                         $row['address'], $row['address1'], $row['city'],
                         $row['stateName'], $row['zip']);
         return $o;
      }
   }

   /**
    * Deletes an Organization from the database.
    * @param object $o Class Organization
    * @return bool Returns TRUE if contact can be deleted.
    */
   function deleteOrganization($o) {
      if (!$o instanceof organization) {
         echo ("Invalid arugument deleteFrom() function<br />");
         return false;
      }
      //Compose the DELETE FROM query string.
      $query = sprintf("DELETE FROM organization WHERE organizationID = %d", $o->getOpportunityID()
      );
      connectToDatabase();
      $result = mysql_query($query);
      mysql_close();
      if ($result == false) {
         echo mysql_error();
         return false;
      }

      //Query was successful.
      return true;
   }

   /**
   * Gets all names and orders them from A-Z
   */
   public static function getAllName() {
      $query = "SELECT organizationID, organizationName FROM organization ORDER BY organizationName ASC";
      connectToDatabase();
      $result = mysql_query($query);
      mysql_close();

      if ($result == false) {
         $output = sprintf("%s", mysql_error());
         return false;
      } else {
         return $result;
      }
   }

   /**
   * Gets all by ID
   * @param getAllByID $id
   */
   public static function getAllByID($id, &$output = null) {
      $query = sprintf("SELECT organizationName, description, address,
            address1, city, stateName, zip FROM organization WHERE
            organizationID=%d", $id);
      //printf("->Query: %s<br />", $query);
      connectToDatabase();
      $result = mysql_query($query);
      mysql_close();

      if ($result == false) {
         $output = sprintf("%s", mysql_error());
         return false;
      }
      //process result and print out warning or attribute values
      return dbOrganization::checkForOneInstance($result, $id, $output);
   }

   /**
   * Check for one instance
   * @param $result
   * @param checkForOneInstance $id
   */
   private static function checkForOneInstance($result, $id, &$output) {
      //Local variables are initialized.
      $errorStatus = false;

      $howMany = mysql_num_rows($result);
      //printf("->Found %d instance(s) for organizationID=%d.<br />", $howMany, $id);
      switch ($howMany) {
         case 0:
            $output = sprintf("No Organization instance with ID=%d was found.", $id);
            $errorStatus = true;
            break;
         case 1:
            //mysql_fetch() DOES NOT return primary key values
            $output = mysql_fetch_assoc($result);
            $errorStatus = false;
            break;
         default:
            $output = sprintf("There are multiple Organization instances with
                                  ID=%d.", $id);
            $errorStatus = true;
            break;
      }
      return $errorStatus;
   }

}

?>
